<?php

//prisiloginimas
$u_query = "SELECT n60_kodas_sm as r05_kodas_rs,n60_pareigos as r05_pareigos,n60_pav as r05_pav,n60_vardas as r05_vardas, n60_e_mail as email, n60_kodas_gs as grupe 
FROM n60_asm where n60_kodas_sm='<get_username>' and n60_pareigos='<get_password>' and n60_poz_date<>1 and n60_kodas_gs in ('WEB','WEB_ADMIN')";

$admin_query = "SELECT * FROM R05_USER WHERE R05_rusis=3 AND R05_KODAS_RS='<get_username>' AND R05_PSW='<get_password>'";

//slaptazodzio keitimas
$pass_query = "exec web_update_password @n60_pareigos='<password>',@n60_kodas_sm='<get_username>',@get_password='<get_password>'";

//slaptazodzio priminimas
$p_query = "SELECT n60_kodas_sm as vartotojo_id, n60_e_mail as email, n60_pareigos as vartotojo_pass
            FROM n60_asm
            where n60_e_mail='<email>' and n60_poz_date=0 and n60_kodas_gs in ('WEB','WEB_ADMIN')";
			
//imone pagal vartotojo id			
$i_query = "SELECT N08_KLIJ.N08_kodas_ks,N08_KLIJ.N08_PVM_KODAS,N08_KLIJ.N08_IM_KODAS,N08_KLIJ.N08_PAV,N08_KLIJ.N08_ADR,N08_KLIJ.N08_PASTAS,N08_KLIJ.N08_KODAS_VS,
			N08_KLIJ.N08_ATSTOVAS,N08_KLIJ.N08_E_MAIL,N08_KLIJ.N08_FAX_NUM,N08_KLIJ.N08_TEL,N08_KLIJ.N08_MOB_TEL,N08_KLIJ.n08_kodas_ts_p, n60_asm.n60_e_mail as r06_pav,
			N08_KLIJ.n08_suma_wk_limit, n24_terd.N24_MOK_D, N08_kodas_ls_5, N08_kodas_ls_6, n09_viet.n09_pav,  n60_kodas_sm
			FROM N08_KLIJ LEFT OUTER JOIN n60_asm ON N08_KLIJ.N08_KODAS_KS = n60_asm.n60_kodas_ks
			LEFT OUTER JOIN n09_viet ON N08_KLIJ.n08_kodas_vs = n09_viet.n09_kodas_vs
			LEFT OUTER JOIN n24_terd ON N08_KLIJ.n08_kodas_ts_p = n24_terd.n24_kodas_ts
			
			where n60_asm.n60_kodas_sm='<kodas>'";		
//imone pagal imones koda			
$ii_query = "SELECT N08_KLIJ.N08_kodas_ks,N08_KLIJ.N08_PVM_KODAS,N08_KLIJ.N08_IM_KODAS,N08_KLIJ.N08_PAV,N08_KLIJ.N08_ADR,N08_KLIJ.N08_PASTAS,N08_KLIJ.N08_KODAS_VS,
			N08_KLIJ.N08_ATSTOVAS,N08_KLIJ.N08_E_MAIL,N08_KLIJ.N08_FAX_NUM,N08_KLIJ.N08_TEL,N08_KLIJ.N08_MOB_TEL,N08_KLIJ.n08_kodas_ts_p, n60_asm.n60_e_mail as r06_pav,
			N08_KLIJ.n08_suma_wk_limit, n24_terd.N24_MOK_D, n60_kodas_sm, n60_pareigos, n09_viet.n09_pav 
			FROM N08_KLIJ LEFT OUTER JOIN n60_asm ON N08_KLIJ.N08_KODAS_KS = n60_asm.n60_kodas_ks
			LEFT OUTER JOIN n09_viet ON N08_KLIJ.n08_kodas_vs = n09_viet.n09_kodas_vs
			LEFT OUTER JOIN n24_terd ON N08_KLIJ.n08_kodas_ts_p = n24_terd.n24_kodas_ts
			
			where N08_KLIJ.N08_kodas_ks='<kodas>'";	
			
//musu imone
$o_query = "SELECT N23_REIKSME1 FROM N23_PRMD where N23_KODAS_PR='SS_INF_KLIJN'";			

//atsiskaitomoji saskaita			
$s_query = "SELECT  N33_KBAN.N33_KODAS_KS, N33_KBAN.N33_KODAS_WS, N34_BANK.N34_PAV, N33_KBAN.N33_S_KODAS, N33_KBAN.N33_NUTYL
FROM    N33_KBAN INNER JOIN N34_BANK ON N33_KBAN.N33_KODAS_WS = N34_BANK.N34_KODAS_WS
where N33_KODAS_KS = '<kodas>' and N33_NUTYL=1";

if (!isset($korteliu_prefiksas)) {
   $korteliu_prefiksas = "N51_PAV like '944%'";
}
//imones korteles
$k_query = "SELECT N51_KODAS_KT,N51_PAV,N51_KODAS_KS,case when (CHARINDEX (';',cast(N51_PASTABOS as varchar(8000)))=5) THEN
SUBSTRING(cast(N51_PASTABOS as varchar(8000)),6,1000) else case when (CHARINDEX (';',cast(N51_PASTABOS as varchar(8000)))=1) then SUBSTRING(cast(N51_PASTABOS as varchar(8000)),2,1000) else
cast(N51_PASTABOS as varchar(8000)) END END as N51_KORTA,cast(N51_PASTABOS as varchar(8000)) as N51_PASTABOS,N51_VISKAS,n51_kodas_ls_1,  N35_LGST.n35_pav, SUBSTRING(N51_PAV,11,6) as rusiavimas
FROM N51_SUTH LEFT OUTER JOIN N35_LGST ON N51_SUTH.n51_kodas_ls_1 = N35_LGST.N35_KODAS_LS
where N51_KODAS_KS='<kodas>' and ".$korteliu_prefiksas." 
order by rusiavimas";

//korteles pavadinimo keitimas
$kchange_query = "exec web_change_card_descr @type='KLIENT', @pastabos ='<pastabos>', @kortele='<kortele>' ,@imkodas='<kodas>'";

$kchange_query_rdata = "exec web_ins_upd_card_info @kortele='<kortele>' ,@nuo='<nuo>' ,@iki='<iki>' ,@credit='<credit>' ,@p1='<p1>' ,@p2='<p2>' ,@p3='<p3>' ,@p4='<p4>' ,@p5='<p5>' ,@p6='<p6>' ,@p7='<p7>',@lustas=<lustas>";

//nauja kortele
$knew_query = "exec web_ins_card @pastabos ='<pastabos>',@imkodas='<kodas>' ,@kortele='<kortele>'";


//korteles blokavimas/atblokavimas
$kblock_query = "exec web_change_card_status @type='KLIENT_BLOK',@action='<block>',@imkodas='<kodas>',@kortele='<kortele>'";

//viena kortele
$card_query = "SELECT N51_PAV,N51_KODAS_KS,cast(N51_PASTABOS as varchar(8000)),case when (CHARINDEX (';',cast(N51_PASTABOS as varchar(8000)))=5) THEN
SUBSTRING(cast(N51_PASTABOS as varchar(8000)),6,1000) else case when (CHARINDEX (';',cast(N51_PASTABOS as varchar(8000)))=1) then SUBSTRING(cast(N51_PASTABOS as varchar(8000)),2,1000) else
cast(N51_PASTABOS as varchar(8000)) END END as N51_KORTA,N51_VISKAS,n51_kodas_ls_1
FROM N51_SUTH
where N51_KODAS_KT='<kodas>' and N51_KODAS_KS='<im_kodas>'";

//viena kortele pagal nr
$card_query_nr = "SELECT N51_PAV,N51_KODAS_KS,cast(N51_PASTABOS as varchar(8000)),case when (CHARINDEX (';',cast(N51_PASTABOS as varchar(8000)))=5) THEN
SUBSTRING(cast(N51_PASTABOS as varchar(8000)),6,1000) else case when (CHARINDEX (';',cast(N51_PASTABOS as varchar(8000)))=1) then SUBSTRING(cast(N51_PASTABOS as varchar(8000)),2,1000) else
cast(N51_PASTABOS as varchar(8000)) END END as N51_KORTA,N51_VISKAS,n51_kodas_ls_1
FROM N51_SUTH
where N51_PAV='<kodas>' and N51_KODAS_KS='<im_kodas>'";

//pardavimai pagal korteles id
$sells_query = "SELECT kvitoGalva.ID, KvitoEilute.GalvosID, KvitoGalva.AparatoID, Aparatai.AparatoPavadinimas, KvitoGalva.Year, KvitoGalva.Month, KvitoGalva.Day,
       KvitoGalva.Hour, KvitoGalva.Minute, KvitoGalva.KortelesNr, KvitoGalva.KvitoNr2, KvitoEilute.Barkodas, KvitoEilute.Kiekis, KvitoEilute.Kaina, KvitoEilute.Suma,
       KvitoEilute.Mokesciai, KvitoGalva.Suma AS Kvitosuma, KvitoEilute.nuolaida, KvitoEilute.nuolaida/KvitoEilute.Kiekis as nuolvienet
FROM Aparatai RIGHT OUTER JOIN kvitoGalva RIGHT OUTER JOIN KvitoEilute ON KvitoGalva.ID = KvitoEilute.GalvosID ON Aparatai.AparatoID = KvitoGalva.AparatoID
WHERE KvitoGalva.KortelesNr = '<kodas>' AND KvitoEilute.Kiekis<>0 AND
cast(cast(KvitoGalva.Year as CHAR(4)) + '-' + cast(KvitoGalva.month as VARCHAR(2)) + '-' + cast(KvitoGalva.day as VARCHAR(2)) as datetime) between '<from>' and '<to>'
order by cast(cast(KvitoGalva.Year as CHAR(4)) + '-' + cast(KvitoGalva.month as VARCHAR(2)) + '-' + cast(KvitoGalva.day as VARCHAR(2)) as datetime),KvitoGalva.hour,KvitoGalva.minute";

//visu imones kortu pardavimai
$sells_all_query = "SELECT kvitoGalva.ID, KvitoEilute.GalvosID, KvitoGalva.AparatoID, Aparatai.AparatoPavadinimas, KvitoGalva.Year, KvitoGalva.Month, KvitoGalva.Day,
       KvitoGalva.Hour, KvitoGalva.Minute, KvitoGalva.KortelesNr, KvitoGalva.KvitoNr2, KvitoEilute.Barkodas, KvitoEilute.Kiekis, KvitoEilute.Kaina, KvitoEilute.Suma,
       KvitoEilute.Mokesciai, KvitoGalva.Suma AS Kvitosuma, KvitoEilute.nuolaida, KvitoEilute.nuolaida/KvitoEilute.Kiekis as nuolvienet
FROM Aparatai RIGHT OUTER JOIN kvitoGalva RIGHT OUTER JOIN KvitoEilute ON KvitoGalva.ID = KvitoEilute.GalvosID ON Aparatai.AparatoID = KvitoGalva.AparatoID
WHERE KvitoGalva.KortelesNr in (<kodas>) AND KvitoEilute.Kiekis<>0 AND
cast(cast(KvitoGalva.Year as CHAR(4)) + '-' + cast(KvitoGalva.month as VARCHAR(2)) + '-' + cast(KvitoGalva.day as VARCHAR(2)) as datetime) between '<from>' and '<to>'
order by KvitoGalva.KortelesNr, cast(cast(KvitoGalva.Year as CHAR(4)) + '-' + cast(KvitoGalva.month as VARCHAR(2)) + '-' + cast(KvitoGalva.day as VARCHAR(2)) as datetime),KvitoGalva.hour,KvitoGalva.minute";

//visu imones kortu pardavimai pagal protokola
$sells_all_protocol = "SELECT kvitoGalva.ID, KvitoEilute.GalvosID, KvitoGalva.AparatoID, Aparatai.AparatoPavadinimas, year(KvitoGalva.data) as Year, month(KvitoGalva.data) as Month, day(KvitoGalva.data) as Day,
       datepart(hour,KvitoGalva.data) as Hour, datepart(minute,KvitoGalva.data) as Minute, KvitoGalva.KortelesNr, KvitoGalva.KvitoNr2, KvitoEilute.Barkodas, KvitoEilute.Kiekis, KvitoEilute.Kaina, KvitoEilute.Suma,
       KvitoEilute.Mokesciai, KvitoGalva.Suma AS Kvitosuma, x02_prot.X02_kaina, case when (x01_prot_n.X01_kodas_ps is not null) then 1 else 0 end as arprotokolo , case when KvitoEilute.nuolaida is null then 0 else KvitoEilute.nuolaida end as nuolaida
FROM Aparatai RIGHT OUTER JOIN kvitoGalva ON Aparatai.AparatoID = KvitoGalva.AparatoID
RIGHT OUTER JOIN KvitoEilute ON KvitoGalva.ID = KvitoEilute.GalvosID 
left OUTER JOIN x02_prot ON KvitoEilute.Barkodas = x02_prot.X02_kodas_ps and cast(KvitoGalva.data as date) = x02_prot.X02_data
left OUTER JOIN x01_prot_n ON KvitoEilute.Barkodas = x01_prot_n.X01_kodas_ps
WHERE KvitoGalva.KortelesNr in (<kodas>) AND KvitoEilute.Kiekis<>0 AND
KvitoGalva.data between '<from> 00:00:00' and '<to> 23:59:59'
order by KvitoGalva.KortelesNr, KvitoGalva.data";


//visu imones kortu pardavimai pagal grupes
$sells_all_query_groups = "SELECT kvitoGalva.ID, KvitoEilute.GalvosID, KvitoGalva.AparatoID, Aparatai.AparatoPavadinimas, KvitoGalva.Year, KvitoGalva.Month, KvitoGalva.Day,
       KvitoGalva.Hour, KvitoGalva.Minute, KvitoGalva.KortelesNr, KvitoGalva.KvitoNr2, KvitoEilute.Barkodas, KvitoEilute.Kiekis, KvitoEilute.Kaina, KvitoEilute.Suma,
       KvitoEilute.Mokesciai, KvitoGalva.Suma AS Kvitosuma, KvitoEilute.nuolaida, KvitoEilute.nuolaida/KvitoEilute.Kiekis as nuolvienet,
	   case when len(LTRIM(RTRIM(KvitoEilute.Barkodas)))=1 then 1 else 0 end as degalai
FROM Aparatai RIGHT OUTER JOIN kvitoGalva RIGHT OUTER JOIN KvitoEilute ON KvitoGalva.ID = KvitoEilute.GalvosID ON Aparatai.AparatoID = KvitoGalva.AparatoID
WHERE KvitoGalva.KortelesNr in (<kodas>) AND KvitoEilute.Kiekis<>0 AND
cast(cast(KvitoGalva.Year as CHAR(4)) + '-' + cast(KvitoGalva.month as VARCHAR(2)) + '-' + cast(KvitoGalva.day as VARCHAR(2)) as datetime) between '<from>' and '<to>'
order by KvitoGalva.KortelesNr, 
len(LTRIM(RTRIM(KvitoEilute.Barkodas))) desc,
cast(cast(KvitoGalva.Year as CHAR(4)) + '-' + cast(KvitoGalva.month as VARCHAR(2)) + '-' + cast(KvitoGalva.day as VARCHAR(2)) as datetime),KvitoGalva.hour,KvitoGalva.minute";


//automatinis prisiloginimas
$auto_query = "SELECT n60_kodas_sm as r05_kodas_rs,n60_pareigos as r05_pareigos,n60_pav as r05_pav,n60_vardas as r05_vardas, n60_e_mail as email, n60_kodas_gs as grupe 
FROM n60_asm where n60_kodas_sm='<get_username>' and n60_poz_date=0";

//imones saskaitos DEB/KRED
$accounts_query = "SELECT i31_kodas_kr,I31_DOK_NR,I31_PASTABOS,I31_PERKELTA,I31_KODAS_LS_4,CONVERT(VARCHAR(10),I31_KLH.I31_OP_DATA,102) AS I31_OP_DATA,
I32_DOK_NR,I32_KODAS_KS,<suma>
FROM    I31_KLH LEFT OUTER JOIN I32_KLD ON I31_KLH.I31_DOK_NR = I32_KLD.I32_DOK_NR
WHERE (I32_KODAS_KS='<im_kodas>') and i31_rusis=6";


//imones saskaitos is pardavimu
$accounts_query2 = "SELECT I06_KODAS_PO,I06_OP_TIP,I06_DOK_NR,CONVERT(VARCHAR(10), I06_OP_DATA, 102) AS I06_OP_DATA,i06_kodas_ks,I06_KODAS_KS+' '+I06_PAV as i06_pav,I06_PASTABOS,
I06_SUMA+I06_SUMA_PVM as i06_suma,i06_perkelta,i06_kodas_ls_4,I06_KODAS_MS,I06_PAV,I06_SUMA,I06_SUMA_PVM
FROM I06_PARH
where (I06_OP_TIP=51 or I06_OP_TIP=52) and (I06_KODAS_KS = '<im_kodas>')";

//apmokejimo terminas
$apmokterm_query = "SELECT I08_MOK_D from I08_part WHERE I08_KODAS_PO = '<kodas>'";

//saskaitos patvirtinimas/nepatvirtinimas
$conf_query = "exec web_change_invoice_status2 @status = '<status>', @kodas='<kodas>'";

//pardavimu saskaitos patvirtinimas/nepatvirtinimas
$conf_query2 = "exec web_change_invoice_status @status = '<status>', @kodas='<kodas>'";

//saskaita pagal dok nr
$sask_query = "SELECT 
	I07_PARD.I07_KODAS, 
	I07_PARD.I07_PAV, 
	I07_PARD.I07_TIPAS,
	cast(sum(I07_PARD.I07_KIEKIS/i07_PARD.I07_FRAKCIJA) as numeric(14,3)) as i07_kiekis, 
	I07_PARD.I07_KODAS_US, 
	cast((sum(I07_PARD.I07_SUMA)/sum(I07_PARD.I07_KIEKIS/i07_PARD.I07_FRAKCIJA)) as numeric(14,4)) as I07_KAINA_BE, 
	I07_PARD.I07_MOKESTIS_P, 
	sum(I07_PARD.I07_SUMA) as I07_SUMA, 
	sum(I07_PARD.I07_PVM) as I07_PVM,
    sum(I07_PARD.i07_nuolaida)	as i07_nuolaida,
	sum(I07_PARD.i07_suma)/sum(I07_PARD.i07_kiekis) as kaina_po
FROM I06_PARH RIGHT OUTER JOIN I07_PARD ON I06_PARH.I06_KODAS_PO = I07_PARD.I07_KODAS_PO
WHERE (I06_PARH.I06_DOK_NR in 
	(
		SELECT I32_KLD.I32_DOK_NR
		FROM I31_KLH RIGHT OUTER JOIN I32_KLD ON I31_KLH.I31_KODAS_KR = I32_KLD.I32_KODAS_KR
		WHERE I31_KLH.I31_DOK_NR = '<dok_nr>' 
		and I31_KLH.I31_DOK_NR<>I32_KLD.I32_DOK_NR
	)
	and I06_PARH.i06_op_data in 
	(
			SELECT I32_KLD.I32_dok_data
			FROM I31_KLH RIGHT OUTER JOIN I32_KLD ON I31_KLH.I31_KODAS_KR = I32_KLD.I32_KODAS_KR
			WHERE I31_KLH.I31_DOK_NR = '<dok_nr>' 
			and I31_KLH.I31_DOK_NR<>I32_KLD.I32_DOK_NR
	)	
) AND (I06_PARH.I06_KODAS_KS = '<im_code>')
group by I07_KODAS, I07_PAV,I07_KODAS_US, I07_MOKESTIS_P, I07_TIPAS";
//nuolaidos suma pagal dok nr
$nuol_query = "SELECT sum(i32_deb_suma) as nuolaida
			FROM I31_KLH RIGHT OUTER JOIN I32_KLD ON I31_KLH.I31_KODAS_KR = I32_KLD.I32_KODAS_KR
			WHERE I31_KLH.I31_DOK_NR = '<dok_nr>' 
			and I31_KLH.I31_DOK_NR=I32_KLD.I32_DOK_NR
			and rtrim(I32_KODAS_IS) <>''";

//pardavimu saskaita pagal id
$sask_query2 = "SELECT I07_KODAS_PO
,I07_EIL_NR
,I07_KODAS
,I07_PAV
,I07_KODAS_OS
,I07_KODAS_OS_C
,I07_SERIJA
,I07_KODAS_US
,case when I07_TIPAS in (1,2,3) then CAST(I07_KIEKIS/I07_FRAKCIJA as numeric(14,3)) else 0 end as i07_kiekis
,I07_KAINA_BE
,I07_KAINA_SU
,I07_NUOLAIDA
,I07_MOKESTIS
,I07_MOKESTIS_P
,I07_PVM
,I07_SUMA
,i07_nuolaida
,I07_KAINA_BE-(I07_KAINA_BE*I07_NUOLAIDA/100) as kaina_po
,I07_TIPAS
FROM I07_PARD
where I07_KODAS_PO='<dok_nr>'";

//pardavimu saskaitos pastabos
$pastabos_query = "SELECT i06_pastabos
FROM I06_PARH
where I06_KODAS_PO='<kodas>'";


//managerio info pagal dok_nr
$manag_query = "SELECT     I32_KLD.I32_KODAS_MS, N15_MEN.N15_PAV, N15_MEN.N15_PAREIGOS
FROM         N15_MEN INNER JOIN
                      I32_KLD ON N15_MEN.N15_KODAS_MS = I32_KLD.I32_KODAS_MS LEFT OUTER JOIN
                      I31_KLH ON I32_KLD.I32_KODAS_KR = I31_KLH.I31_KODAS_KR
WHERE     (I31_KLH.I31_DOK_NR = '<dok_nr>') AND (I31_KLH.I31_DOK_NR <> I32_KLD.I32_DOK_NR)
 AND (I32_KLD.I32_KODAS_MS <> '')";

//managerio info pagal manager id
$manag_query2 = "SELECT     I06_PARH.I06_KODAS_MS as I32_KODAS_MS, N15_MEN.N15_PAV, N15_MEN.N15_PAREIGOS
FROM         N15_MEN INNER JOIN
                      I06_PARH ON N15_MEN.N15_KODAS_MS = I06_PARH.I06_KODAS_MS 
WHERE     (I06_PARH.I06_KODAS_MS = '<man_id>') ";

 
 //fakturu seriojos
$fakt_query = "SELECT N23_REIKSME1 FROM N23_PRMD where N23_KODAS_PR='WEB_SERIJA'";

//prekes info pagal barkoda
$prek_query = "select n37_pav,n37_kodas_us from n37_pmat where CAST(N37_BAR_KODAS AS bigINT)='<barkodas>'";

//imones skola
$skola_query = "SELECT sum(I44_SUMA_DB)-sum(I44_SUMA_CR) as skola
FROM I44_SKOL WITH (NOLOCK)
where i44_kodas_ks='<im_kodas>'
group by i44_kodas_ks";

//imones neapmoketos sask


$ns_query2 = "select i44_dok_nr,sum(i44_suma_db) as db,sum(i44_suma_cr) as cr
from i44_skol where i44_kodas_ks = '<im_kodas>' 
group by i44_dok_nr
having sum(i44_suma_db)<>sum(i44_suma_cr) ";
$ns_query3 = "select
				CONVERT(VARCHAR(10),I44_DATA_DOK, 102) as I44_DATA_DOK, 
				CONVERT(VARCHAR(10),I44_DATA_MOK, 102) as I44_DATA_MOK,
				DATEDIFF(DD,GETDATE(),I44_DATA_MOK) as dienos
				from I44_SKOL where i44_kodas_ks = '<im_kodas>' AND I44_DOK_NR = '<dok_nr>' 
				GROUP BY I44_DATA_DOK,I44_DATA_MOK,DATEDIFF(DD,GETDATE(),I44_DATA_MOK)
				order by I44_DATA_DOK desc ";

//gauti imones mokejimai


$mokejimai_query = "select top 5 i04_dok_nr,CONVERT(VARCHAR(10), i04_op_data, 102) AS i04_op_data,i04_suma from i04_ath
where i04_kodas_ks='<im_kodas>' and i04_op_rusis=1 and I04_PERKELTA=2 and i04_suma <> 0
order by i04_op_data desc";

//visi vartotojai
$allusers_query = "SELECT n60_kodas_sm,n60_e_mail,n60_pav2,n60_poz_date,n60_skyrius,n60_pareigos FROM n60_asm where n60_kodas_gs in ('WEB') <filter> order by n60_pav2";

//vartotojo blokavimas/atblokavimas
$ustatus_query = "exec web_change_user_status @status = '<status>', @kodas='<kodas>'";

//vartotojo info
$uinfo_query = "SELECT n60_kodas_ks, n60_pav2, n60_pareigos, n60_vardas, n60_pav, n60_e_mail, n60_skyrius as n60_poz_date, n60_kodas_sm FROM n60_asm WHERE n60_kodas_sm = '<kodas>' AND n60_kodas_gs in ('WEB')";

//paskutine imones saskaita + 1 d
$lastaccdate_query = "SELECT CONVERT(VARCHAR(10), DATEADD(DAY, 1,MAX(I31_KLH.I31_OP_DATA)), 102) AS I31_OP_DATA
FROM I31_KLH LEFT OUTER JOIN I32_KLD ON I31_KLH.I31_DOK_NR = I32_KLD.I32_DOK_NR
WHERE I32_KODAS_KS='<kodas>' and i31_rusis=6 ";

//imones panaudotas kreditas
$imusedcredit_query = "SELECT sum(KvitoGalva.Suma) AS Kvitosuma
FROM kvitoGalva WITH (NOLOCK)
WHERE KvitoGalva.KortelesNr in (<kodas>) AND 
KvitoGalva.data between '<from> 00:00:00' and '<to> 23:59:59' ";

//nestandartine nuolaida
$nestnuol_q = "SELECT sum(i32_cre_suma) as nuolaida
		FROM I31_KLH RIGHT OUTER JOIN I32_KLD ON I31_KLH.I31_KODAS_KR = I32_KLD.I32_KODAS_KR 
		WHERE I31_KLH.I31_DOK_NR = '<dok_nr>' AND I32_DOK_NR = 'NUOLAIDA'
and i32_kodas_ss_k = '50001'";
//nestandartines nuolaidos pvm
$nestnuolpvm_q = "SELECT sum(i32_cre_suma) as nuolpvm
		FROM I31_KLH RIGHT OUTER JOIN I32_KLD ON I31_KLH.I31_KODAS_KR = I32_KLD.I32_KODAS_KR 
		WHERE I31_KLH.I31_DOK_NR = '<dok_nr>' AND I32_DOK_NR = 'NUOLAIDA'
and i32_kodas_ss_k = '44840'";

$log_query = "SELECT TOP 200 [id]
      ,CONVERT(VARCHAR(20), [data], 120) as data
      ,[vart_id]
      ,[vart_pavad]
      ,[veiksmas]
      ,[vart_ip]
      ,[kodas]
	  ,[type_pavad]
  FROM [History] ,[history_type]
  WHERE [History].[kodas]=[history_type].[type_kodas] and <data> <kodas> <vart_id>
  order by id desc";
  
$debkred_new="SELECT COUNT(*) as cnt FROM I31_KLH INNER  JOIN I32_KLD ON I31_KLH.I31_DOK_NR = I32_KLD.I32_DOK_NR 
WHERE (I32_KODAS_KS IN 
(SELECT N08_kodas_ks FROM N08_KLIJ 
INNER JOIN n60_asm ON N08_KLIJ.N08_KODAS_KS = n60_asm.n60_kodas_ks 
and n60_asm.n60_kodas_sm in (SELECT n60_kodas_sm FROM n60_asm 
where n60_poz_date<>1 and n60_kodas_gs='WEB'))) 
and i31_rusis=6 and (I31_KODAS_LS_4 is null) 
AND I31_PERKELTA=2";
$debkred_1P="SELECT COUNT(*) as cnt FROM I31_KLH where i31_rusis=6 and I31_KODAS_LS_4 = '1P' AND I31_PERKELTA=2";
$debkred_2P="SELECT COUNT(*) as cnt FROM I31_KLH where i31_rusis=6 and I31_KODAS_LS_4 = '2P' AND I31_PERKELTA=2";  
$debkred_3P="SELECT COUNT(*) as cnt FROM I31_KLH where i31_rusis=6 and I31_KODAS_LS_4 = '3P' AND I31_PERKELTA=2";
$debkred_4P="SELECT COUNT(*) as cnt FROM I31_KLH where i31_rusis=6 and I31_KODAS_LS_4 = '4P' AND I31_PERKELTA=2";
$debkred_5P="SELECT COUNT(*) as cnt FROM I31_KLH where i31_rusis=6 and I31_KODAS_LS_4 = '5P' AND I31_PERKELTA=2";
$debkred_nep="SELECT COUNT(*) as cnt
FROM I31_KLH 
WHERE 
 i31_rusis=6 and I31_KODAS_LS_4 = '-'
AND I31_PERKELTA=2";

$pard_new = "SELECT COUNT(*) as cnt
FROM I06_PARH where (I06_OP_TIP=51 or I06_OP_TIP=52) 
and (i06_kodas_ls_4 is null) 
and (I06_DOK_NR like '<SERIJA>%')";

$pard_1p = "SELECT COUNT(*) as cnt
FROM I06_PARH where (I06_OP_TIP=51 or I06_OP_TIP=52) 
and i06_kodas_ls_4 = '1P'
and (I06_DOK_NR like '<SERIJA>%')";

$pard_2p = "SELECT COUNT(*) as cnt
FROM I06_PARH where (I06_OP_TIP=51 or I06_OP_TIP=52) 
and i06_kodas_ls_4 = '2P'
and (I06_DOK_NR like '<SERIJA>%')";

$pard_3p = "SELECT COUNT(*) as cnt
FROM I06_PARH where (I06_OP_TIP=51 or I06_OP_TIP=52) 
and i06_kodas_ls_4 = '3P'
and (I06_DOK_NR like '<SERIJA>%')";

$pard_4p = "SELECT COUNT(*) as cnt
FROM I06_PARH where (I06_OP_TIP=51 or I06_OP_TIP=52) 
and i06_kodas_ls_4 = '4P'
and (I06_DOK_NR like '<SERIJA>%')";

$pard_5p = "SELECT COUNT(*) as cnt
FROM I06_PARH where (I06_OP_TIP=51 or I06_OP_TIP=52) 
and i06_kodas_ls_4 = '5P'
and (I06_DOK_NR like '<SERIJA>%')";

$pard_nep = "SELECT COUNT(*) as cnt
FROM I06_PARH where (I06_OP_TIP=51 or I06_OP_TIP=52) 
and i06_kodas_ls_4 = '-'
and (I06_DOK_NR like '<SERIJA>%')";

$aktas_query = "SELECT sum(I44_SUMA_DB)-sum(I44_SUMA_CR) as skola
FROM I44_SKOL WITH (NOLOCK)
where i44_kodas_ks='<kodas>' and I44_DATA_DOK<='<data>'
group by i44_kodas_ks";

$comdel_query = "DELETE FROM comment WHERE dok_nr='<dok_nr>'";
$comins_query = "INSERT INTO comment (dok_nr,tekstas) VALUES ('<dok_nr>','<comment>')";
$comsel_query = "SELECT tekstas FROM comment WHERE dok_nr='<dok_nr>'";


$track_query = "SELECT N35_KODAS_LS,N35_PAV FROM N35_LGST  where n35_tevas='KRED_SEKIMAS' order by N35_KODAS_LS";

$overview_query = "select Aparatai.AparatoID,Aparatai.AparatoPavadinimas, Aparatai.velavimas, MAX(KvitoGalva.ID) as id
from Aparatai
left outer join  KvitoGalva on Aparatai.AparatoID=KvitoGalva.AparatoID
where Aparatai.apzvalga is not null and Aparatai.apzvalga=1
group by Aparatai.AparatoID,Aparatai.AparatoPavadinimas,Aparatai.velavimas";

$overview_det_query = "select KvitoGalva.Znr,KvitoGalva.Suma,KvitoGalva.Nuolaida,KvitoGalva.KortelesNr,CONVERT(VARCHAR(16),cast(cast(KvitoGalva.Year as CHAR(4)) + '-' + cast(KvitoGalva.month as VARCHAR(2)) + '-' + cast(KvitoGalva.day as VARCHAR(2)) + ' ' + cast(KvitoGalva.hour as VARCHAR(2)) + ':' + cast(KvitoGalva.minute as VARCHAR(2)) as datetime),120) as data,
case when (CURRENT_TIMESTAMP > DATEADD(hour,case when (Aparatai.velavimas is null or Aparatai.velavimas = 0) then 24 else Aparatai.velavimas*2 end,cast(cast(KvitoGalva.Year as CHAR(4)) + '-' + cast(KvitoGalva.month as VARCHAR(2)) + '-' + cast(KvitoGalva.day as VARCHAR(2)) + ' ' + cast(KvitoGalva.hour as VARCHAR(2)) + ':' + cast(KvitoGalva.minute as VARCHAR(2)) as datetime))) then 'red' else
 case when (CURRENT_TIMESTAMP > DATEADD(hour, case when (Aparatai.velavimas is null or Aparatai.velavimas = 0) then 12 else Aparatai.velavimas end ,cast(cast(KvitoGalva.Year as CHAR(4)) + '-' + cast(KvitoGalva.month as VARCHAR(2)) + '-' + cast(KvitoGalva.day as VARCHAR(2)) + ' ' + cast(KvitoGalva.hour as VARCHAR(2)) + ':' + cast(KvitoGalva.minute as VARCHAR(2)) as datetime))) then 'yellow'
 else 'green'
 end
end as color
 from KvitoGalva, Aparatai where KvitoGalva.ID = <KvitoId> AND Aparatai.AparatoID = KvitoGalva.AparatoID";

$vouncher_query ="SELECT ROW_NUMBER() OVER ( ORDER BY KvitoGalva.ID) as RowNum,KvitoGalva.*,KvitoEilute.Suma as Suma1, KvitoEilute.Mokesciai as Mokesciai1,KvitoEilute.Nuolaida as Nuolaida1,KvitoEilute.Barkodas as Barkodas1,KvitoEilute.Kiekis as Kiekis1,CONVERT(VARCHAR(16),KvitoGalva.data,120) as data1 FROM KvitoGalva 
left outer join  KvitoEilute on KvitoEilute.GalvosID=KvitoGalva.ID
WHERE <import> "; 

//kvito korteles keitimas
$vchange_query = "exec web_upd_vouncher @id ='<id>', @kortele='<kortele>'";

//kvito trynimas
$vdel_query = "exec web_del_vouncher @id ='<id>'";

$rdel_query = "exec web_del_register @id ='<id>'";

$reginfo_query = "SELECT * FROM N90_registras WHERE N90_id = <kodas>";

$allcards_query = "SELECT 
N51_KODAS_KT,N51_PAV,N51_KODAS_KS,
case 
	when (CHARINDEX (';',cast(N51_PASTABOS as varchar(8000)))=5) 
	THEN
		SUBSTRING(cast(N51_PASTABOS as varchar(8000)),6,1000) 
	else 
		case 
			when (CHARINDEX (';',cast(N51_PASTABOS as varchar(8000)))=1) 
			then 
				SUBSTRING(cast(N51_PASTABOS as varchar(8000)),2,1000) 
			else
				cast(N51_PASTABOS as varchar(8000)) 
		END 
	END as N51_KORTA,
N35_LGST.n35_pav,
N51_VISKAS,a.n60_pav2,a.n60_kodas_sm
FROM 
N60_asm a,
N51_SUTH LEFT OUTER JOIN N35_LGST ON N51_SUTH.n51_kodas_ls_1 = N35_LGST.N35_KODAS_LS
where 
N51_KODAS_KS=a.N60_KODAS_KS
and <aktyvios> 
and a.n60_kodas_sm like '%<uid>%'
and a.n60_pav2 LIKE '%<pav>%'
and a.n60_kodas_gs in ('WEB')
and n51_pav LIKE '<cnumber>%'
and N51_PASTABOS LIKE '%<cdesc>%'
and [N51_KODAS_LS_8]<>'NUOLAIDA'
";

//nuolaidos trynimas
$ndel_query = "exec web_del_discount @id ='<id>'";

$terminals_query = "select Aparatai.AparatoID,Aparatai.AparatoPavadinimas,Aparatai.apzvalga, ws_roles_h.role_pav as role_id
from Aparatai

left outer join  ws_users on Aparatai.AparatoID=ws_users.aparatoid
left outer join  ws_roles_h on ws_roles_h.role_id=ws_users.role_id
group by Aparatai.AparatoID,Aparatai.AparatoPavadinimas,Aparatai.apzvalga, ws_roles_h.role_pav
";

//roles trynimas
$roledel_query = "exec web_del_role @id ='<id>'";

//roles keitimas
$role_change_query = "exec web_ins_upd_role @uid ='<uid>', @pav='<pav>'";
//aparato info keitimas
$terminal_change_query = "exec web_ins_upd_ws_users @uid ='<uid>', @pav='<pav>', @role_id='<role_id>', @pass='<pass>', @apzvalga='<apzvalga>'";

//aparato info keitimas rivileje
$terminal_changer_query = "exec web_update_aparatas @uid ='<uid>', @pid='<pid>', @lastz='<lastz>'";

//naujas aparatas rivileje
$aparatas_insertr_query = "exec web_insert_aparatas @uid ='<uid>', @pid='<pid>', @lastz='<lastz>'";


//naujas aparatas rdatoje
$aparatas_insert_query = "exec web_insert_aparatas @uid ='<uid>'";

//aparato trynimas
$terminal_del_query = "exec web_del_ws_users @uid ='<uid>'";

//aparato trynimas rivileje
$terminal_delr_query = "exec web_del_aparatas @uid ='<uid>'";

 ?>